package edu.jhu.secondhome.database.accessors;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.jhu.secondhome.database.ConnectionPool;
import edu.jhu.secondhome.database.DataUtil;
import edu.jhu.secondhome.database.beans.Reservation;

public class ReservationAccessor extends Accessor {
	
	/**
	 * Gets all reservations from the database.
	 * @return List<Reservation>, empty if none exist
	 * @throws SQLException
	 */
	public List<Reservation> getReservations() throws SQLException {
		List<Reservation> reservs = new ArrayList<Reservation>();
		
		final ConnectionPool pool = ConnectionPool.getInstance();
		final Connection conn = pool.getConnection();
		PreparedStatement ps = null;
		
		try {
			ps = conn.prepareStatement("SELECT * FROM Reservations");
			ResultSet rs = ps.executeQuery();
			// Move to first result:
			rs.next();
			// Process results
			while (!rs.isAfterLast()) {
				reservs.add(extractReservation(rs));
				rs.next();
			}
		} finally {
			DataUtil.closeStatement(ps);
			pool.freeConnection(conn);
		}
		return reservs;
	}
	
	/**
	 * Gets all reservations from the database for the user
	 * @param email - email address of the user
	 * @return List<Reservation>, empty if none exist
	 * @throws SQLException
	 */
	public List<Reservation> getReservationsForUser(final String email) throws SQLException {
		List<Reservation> reservs = new ArrayList<Reservation>();
		final ConnectionPool pool = ConnectionPool.getInstance();
		final Connection conn = pool.getConnection();
		PreparedStatement ps = null;
		
		try {
			ps = conn.prepareStatement("SELECT * FROM Reservations WHERE email = ?");
			ps.setString(1, email);
			ResultSet rs = ps.executeQuery();
			// Move to first result: 
			rs.next();
			// Process results
			while (!rs.isAfterLast()) {
				reservs.add(extractReservation(rs));
				rs.next();
			}
		} finally {
			DataUtil.closeStatement(ps);
			pool.freeConnection(conn);
		}
		
		return reservs;
	}
	
	// TODO: getReservationsInRange() , getReservationsForUserInRange() , 
	// 			getReservationsForHotelInRange() for date ranges?
	
	
	
	/**
	 * Returns Reservation with specified id, or null if doesn't exist.
	 * @param id - reservation id
	 * @return - Reservation information, null if does not exist
	 * @throws SQLException
	 */
	public Reservation getReservationById(final Integer id) throws SQLException {
		Reservation res = null;
		final ConnectionPool pool = ConnectionPool.getInstance();
		final Connection conn = pool.getConnection();
		PreparedStatement ps = null;
		
		try {
			ps = conn.prepareStatement("SELECT * FROM Reservations WHERE reservationId = ?");
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			// Move to first result: 
			if (rs.next())
				res = extractReservation(rs);
		} finally {
			DataUtil.closeStatement(ps);
			pool.freeConnection(conn);
		}
		
		return res;
	}
	
	/**
	 * Returns Reservation user email with specified id, or null if doesn't exist.
	 * @param id - reservation id
	 * @return - Reservation's email information, null if does not exist
	 * @throws SQLException
	 */
	public String getReservationEmailById(final Integer id) throws SQLException {
		String email = null;
		final ConnectionPool pool = ConnectionPool.getInstance();
		final Connection conn = pool.getConnection();
		PreparedStatement ps = null;
		
		try {
			ps = conn.prepareStatement("SELECT email FROM Reservations WHERE reservationId = ?");
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			// Move to first result: 
			if (rs.next())
				email = rs.getString("email");
		} finally {
			DataUtil.closeStatement(ps);
			pool.freeConnection(conn);
		}
		
		return email;
	}
	
	/**
	 * Gets all reservations from the database for the hotel
	 * @param hotelName 
	 * @return List<Reservation>, empty if none exist
	 * @throws SQLException
	 */
	public List<Reservation> getReservationsForHotel(final String hotelName) throws SQLException {
		List<Reservation> reservs = new ArrayList<Reservation>();
		final ConnectionPool pool = ConnectionPool.getInstance();
		final Connection conn = pool.getConnection();
		PreparedStatement ps = null;
		
		try {
			ps = conn.prepareStatement("SELECT * FROM Reservations WHERE hotelName = ?");
			ps.setString(1, hotelName);
			ResultSet rs = ps.executeQuery();
			// Move to first result: 
			rs.next();
			// Process results
			while (!rs.isAfterLast()) {
				reservs.add(extractReservation(rs));
				rs.next();
			}
		} finally {
			DataUtil.closeStatement(ps);
			pool.freeConnection(conn);
		}
		
		return reservs;
	}
	
	/**
	 * Creates a new reservation in the database
	 * @param res - the Reservation to add
	 * @return int (from PreparedStatement: the row count for SQL Data 
	 * 			Manipulation Language (DML) statements or 0 for statements 
	 * 			that return nothing)
	 * @throws SQLException
	 */
	public int insertReservation(Reservation res) throws SQLException {
		String sql = "INSERT INTO Reservations (email, hotelName, " +
			"roomNumber, startDate, numOfDays, numOfPeople, totalCost" + 
			") VALUES (?, ?, ?, ?, ?, ?, ?)";
		Object[] params = {
				res.getEmail(),
				res.getHotelName(),
				res.getRoomNumber(),
				res.getStartDate(),
				res.getNumOfDays(),
				res.getNumOfPeople(),
				res.getTotalCost()
		};
		return executeUpdateStatement(sql, params);
	}
	
	/**
	 * Updates a reservation in the database
	 * @param res - the reservation with updated values
	 * @return int (from PreparedStatement: the row count for SQL Data 
	 * 			Manipulation Language (DML) statements or 0 for statements 
	 * 			that return nothing)
	 * @throws SQLException
	 */
	public int updateReservation(Reservation res) throws SQLException {
		String sql = "UPDATE Reservations email = ?, " +
			"hotelName = ?, roomNumber = ?, startDate = ?, " +
			"numOfDays = ?, numOfPeople = ?, totalCost = ? " + 
			"WHERE reservationId = ?";
		Object[] params = {
				res.getEmail(),
				res.getHotelName(),
				res.getRoomNumber(),
				res.getStartDate(),
				res.getNumOfDays(),
				res.getNumOfPeople(),
				res.getTotalCost(),
				res.getReservationId()
		};
		return executeUpdateStatement(sql, params);
	}
	
	/**
	 * Updates a reservation in the database
	 * @param id - the reservation identifier
	 * @param roomNumber - the new room number
	 * @param newCost - the new total cost based on the room selection
	 * @return int (from PreparedStatement: the row count for SQL Data 
	 * 			Manipulation Language (DML) statements or 0 for statements 
	 * 			that return nothing)
	 * @throws SQLException
	 */
	public int updateReservation(final Integer id, final String roomNumber, final Integer newCost) throws SQLException {
		String sql = "UPDATE Reservations SET roomNumber = ?, totalCost = ? " + 
			"WHERE reservationId = ?";
		Object[] params = {
				roomNumber,
				newCost,
				id
		};
		return executeUpdateStatement(sql, params);
	}
	
	/**
	 * Deletes a reservation in the database
	 * @param res - reservation to delete (with reservationId filled)
	 * @return int (from PreparedStatement: the row count for SQL Data 
	 * 			Manipulation Language (DML) statements or 0 for statements 
	 * 			that return nothing)
	 * @throws SQLException
	 */
	public int deleteReservation(Reservation res) throws SQLException {
		return deleteReservation(res.getReservationId());
	}
	
	/**
	 * Deletes a reservation in the database
	 * @param id - the id of the reservation to delete
	 * @return int (from PreparedStatement: the row count for SQL Data 
	 * 			Manipulation Language (DML) statements or 0 for statements 
	 * 			that return nothing)
	 * @throws SQLException
	 */
	public int deleteReservation(int id) throws SQLException {
		String sql = "DELETE FROM Reservations " + 
			"WHERE reservationId = ?";
		Integer[] params = { id };
		
		return executeUpdateStatement(sql, params);
	}
	
	/**
	 * Helper method to extract the reservation information from the 
	 * ResultSet row and put it into the bean
	 * @param rs - ResultSet pointing to the row of data to pull from
	 * @return populated Reservation object
	 * @throws SQLException
	 */
	private Reservation extractReservation(ResultSet rs) throws SQLException {
		return new Reservation(rs.getInt("reservationId"), rs.getString("email"), 
				rs.getString("hotelName"), rs.getString("roomNumber"), 
				rs.getDate("startDate"), rs.getInt("numOfDays"), 
				rs.getInt("numOfPeople"), rs.getInt("totalCost"));
	}
}
